/**
	EXEC SP_FORECAST_WATER_FALL_CHART_REPORT 0, 0, 4, 0, 0, 2010, 7, 18	
*/
ALTER  PROC SP_FORECAST_WATER_FALL_CHART_REPORT(
	@PROJID INT,
	@PRODID INT,
	@CUSTID INT,
	@SPID	INT, --salesperson id
	@SNAPSHOT_START_MONTH INT, 
	@SNAPSHOT_START_YEAR INT, 
	@SHOW_NEXT INT, 	--NUMBER OF SNAPSHOT MONTHS INCLUDING THE START MONTH
	@SHOW_NEXT_FC_DETAILS INT --NUMBER FOR FORECAST MONTHS(FORECAST DETAILS) TO BE CONSIDERED, THIS FIELD IS USED TO GET THE BACKLOG, SHIPPED DETAILS FOR THESE MONTHS.
 )
 AS
 BEGIN
	DECLARE @RESULT_TABLE TABLE (		
			SNAPSHOT_MONTH INT,
			SNAPSHOT_YEAR INT,
			MONTH		INT,
			YEAR		INT,
			QTY		INT,
			PRICE		FLOAT,
			CONFIDENCE_LEVEL FLOAT,
			SNAPSHOT_NAME VARCHAR(100)
			)

	DECLARE @SHORT_MONTH_NAME VARCHAR(10)
	DECLARE @SNAPSHOT_NAME VARCHAR(50)
	DECLARE @MONTH_COUNT 	INT
	DECLARE @MONTH 		INT
	DECLARE @YEAR 		INT
	DECLARE @QTY 		INT
	DECLARE @PRICE 		FLOAT
	DECLARE @CONFIDENCE_LEVEL FLOAT
	DECLARE @CUSTID_LIKE VARCHAR(50)
	DECLARE @PRODID_LIKE VARCHAR(50)
	DECLARE @SPID_LIKE VARCHAR(50)
	DECLARE @PROJID_LIKE VARCHAR(50)
	DECLARE @CURRENT_MONTH INT
	DECLARE @CURRENT_YEAR INT
	DECLARE @START_DATE DATETIME
	DECLARE @END_DATE DATETIME

	IF(@PROJID IS NULL OR @PROJID <= 0)	
		SET @PROJID_LIKE = '%'
	ELSE
		SET @PROJID_LIKE = CONVERT(VARCHAR, @PROJID)

	IF(@CUSTID IS NULL OR @CUSTID <= 0)	
		SET @CUSTID_LIKE = '%'
	ELSE
		SET @CUSTID_LIKE = CONVERT(VARCHAR, @CUSTID) 

	IF(@PRODID IS NULL OR @PRODID <= 0)	
		SET @PRODID_LIKE = '%'
	ELSE
		SET @PRODID_LIKE = CONVERT(VARCHAR, @PRODID) 

	IF(@SPID IS NULL OR @SPID <= 0)	
		SET @SPID_LIKE = '%'
	ELSE
		SET @SPID_LIKE = CONVERT(VARCHAR, @SPID)
	

	SET @CURRENT_MONTH	= MONTH(GETDATE())-1 --zero based
	SET @CURRENT_YEAR	= YEAR(GETDATE())

	SET @MONTH	= @SNAPSHOT_START_MONTH
	SET @YEAR	= @SNAPSHOT_START_YEAR

	SET @MONTH_COUNT= 0
	WHILE ( @MONTH_COUNT < @SHOW_NEXT )
	 BEGIN
		IF ( @MONTH = 12 )
		 BEGIN
			SET @MONTH = 0
			SET @YEAR  = @YEAR + 1
		 END
		SET @SHORT_MONTH_NAME = left( datename( month, dateadd(month, @MONTH, 0)), 3 )
		SET @SNAPSHOT_NAME = @SHORT_MONTH_NAME + '-' + CONVERT(VARCHAR, @YEAR)
		SET @QTY		= 0
		SET @PRICE		= 0
		SET @CONFIDENCE_LEVEL 	= 0
		
		--snapshot details are not available
		IF( (SELECT COUNT(*) FROM FORECAST_SNAPSHOT_TABLE WHERE SNAPSHOT_NAME = @SNAPSHOT_NAME AND PROJID LIKE @PROJID_LIKE
					AND PRODID LIKE @PRODID_LIKE
					AND CUSTID LIKE @CUSTID_LIKE
					AND SPERSONID LIKE @SPID_LIKE) = 0 )
		 BEGIN			
			--current month snapshot details are not available so considering fcentrymaster values from current month and current year
			IF( @MONTH = @CURRENT_MONTH AND @YEAR = @CURRENT_YEAR )
			 BEGIN
				INSERT INTO @RESULT_TABLE (
									SNAPSHOT_MONTH,
									SNAPSHOT_YEAR,
									MONTH,
									YEAR,
									QTY,
									PRICE,
									CONFIDENCE_LEVEL,
									SNAPSHOT_NAME)
				SELECT @MONTH, @YEAR, MONTH, YEAR, QTY, PRICE, CONFIDENCE_LEVEL, @SNAPSHOT_NAME FROM FCENTRYMASTER 
					WHERE [YEAR] > @CURRENT_YEAR OR ( [MONTH] >= @CURRENT_MONTH AND @CURRENT_YEAR = [YEAR])
						AND PROJID LIKE @PROJID_LIKE
						AND PRODID LIKE @PRODID_LIKE
						AND CUSTID LIKE @CUSTID_LIKE
						AND SPERSONID LIKE @SPID_LIKE
			 END
			ELSE
			 BEGIN
				--INSERTING DUMMY RECORD FOR THE SNAPSHOT NAME
				INSERT INTO @RESULT_TABLE (
									SNAPSHOT_MONTH,
									SNAPSHOT_YEAR,
									MONTH,
									YEAR,
									QTY,
									PRICE,
									CONFIDENCE_LEVEL,
									SNAPSHOT_NAME)
					VALUES(@MONTH, @YEAR, @MONTH, @YEAR, 0, 0, 0, @SNAPSHOT_NAME)
			 END
 
		 END
		INSERT INTO @RESULT_TABLE (
								SNAPSHOT_MONTH,
								SNAPSHOT_YEAR,
								MONTH,
								YEAR,
								QTY,
								PRICE,
								CONFIDENCE_LEVEL,
								SNAPSHOT_NAME)
			SELECT @MONTH, @YEAR, MONTH, YEAR, QTY, PRICE, CONFIDENCE_LEVEL, SNAPSHOT_NAME FROM FORECAST_SNAPSHOT_TABLE 
				WHERE SNAPSHOT_NAME = @SNAPSHOT_NAME 
					AND PROJID LIKE @PROJID_LIKE
					AND PRODID LIKE @PRODID_LIKE
					AND CUSTID LIKE @CUSTID_LIKE
					AND SPERSONID LIKE @SPID_LIKE
		SET @MONTH = @MONTH + 1
		SET @MONTH_COUNT = @MONTH_COUNT + 1
	 END
	SET @MONTH	= @SNAPSHOT_START_MONTH
	SET @YEAR	= @SNAPSHOT_START_YEAR
	SET @MONTH_COUNT= 0
	WHILE ( @MONTH_COUNT < @SHOW_NEXT_FC_DETAILS )
	 BEGIN
		IF ( @MONTH = 12 )
		 BEGIN
			SET @MONTH = 0
			SET @YEAR  = @YEAR + 1
		 END
		SET @START_DATE = CONVERT(DATETIME, CONVERT( VARCHAR, @MONTH+1 )+'/01/'+ CONVERT( VARCHAR, @YEAR ))
		SET @END_DATE	= DATEADD( mi, -1 , DATEADD( m, 1, CONVERT(DATETIME, @START_DATE ) ) )

		SET @QTY = 0
		SET @PRICE = 0

		SET @QTY = (SELECT SUM(CSLIT_Qty_SHIPPED) FROM Customer_Shipping_Line_Item_Table CSLIT, CUSTOMER_OPEN_ORDER_LINEITEM COOL, SALES_ORDER SO
			WHERE CSLIT.LineItem_ID = COOL.LineItem_ID AND COOL.SALESORDER_ID = SO.SALESORDER_ID
				AND CSLIT_SHIPPED_DATE >= @START_DATE AND  CSLIT_SHIPPED_DATE <= @END_DATE
				AND CUSTOMER_ID LIKE @CUSTID_LIKE
				AND COOL.Product_ID LIKE @PRODID_LIKE
				AND SO.SO_SALES_PERSON LIKE (CASE WHEN @SPID_LIKE = '%' THEN @SPID_LIKE ELSE (select SalesPerson_Name from Sales_Persons WHERE SalesPerson_ID = @SPID) END) 
				AND CSLIT.CSLIT_STATUS = 'SHIPPED')		
		SET @PRICE = (SELECT AVG(COOL_UNIT_COST) FROM Customer_Shipping_Line_Item_Table CSLIT, CUSTOMER_OPEN_ORDER_LINEITEM COOL, SALES_ORDER SO
			WHERE CSLIT.LineItem_ID = COOL.LineItem_ID AND COOL.SALESORDER_ID = SO.SALESORDER_ID 
				AND CSLIT_SHIPPED_DATE >= @START_DATE AND  CSLIT_SHIPPED_DATE <= @END_DATE
				AND CUSTOMER_ID LIKE @CUSTID_LIKE
				AND COOL.Product_ID LIKE @PRODID_LIKE
				AND SO.SO_SALES_PERSON LIKE (CASE WHEN @SPID_LIKE = '%' THEN @SPID_LIKE ELSE (select SalesPerson_Name from Sales_Persons WHERE SalesPerson_ID = @SPID) END) 				
				AND CSLIT.CSLIT_STATUS = 'SHIPPED')
		
		INSERT INTO @RESULT_TABLE (
								SNAPSHOT_MONTH,
								SNAPSHOT_YEAR,
								MONTH,
								YEAR,
								QTY,
								PRICE,
								CONFIDENCE_LEVEL,
								SNAPSHOT_NAME)
			VALUES ( @CURRENT_MONTH, @CURRENT_YEAR, @MONTH, @YEAR, @QTY, @PRICE, 0, 'Actual Shipments')
		
		SET @QTY = 0
		SET @PRICE = 0
		--Backlog will be taken from current month and year.. previous months backlog is not considered
		IF(@YEAR > @CURRENT_YEAR OR ( @MONTH >= @CURRENT_MONTH AND @CURRENT_YEAR = @YEAR))
		 BEGIN
			SELECT @QTY = SUM(COOL_QTY) FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL, SALES_ORDER SO 
				WHERE COOL.SALESORDER_ID = SO.SALESORDER_ID 					
					AND SO.CUSTOMER_ID LIKE @CUSTID_LIKE
					AND COOL.Product_ID LIKE @PRODID_LIKE
					AND SO.SO_SALES_PERSON LIKE (CASE WHEN @SPID_LIKE = '%' THEN @SPID_LIKE ELSE (select SalesPerson_Name from Sales_Persons WHERE SalesPerson_ID = @SPID) END) 
					AND COOL_STATUS != 'SHIPPED' 
					AND COOL_REQUESTED_DATE >= @START_DATE 
					AND COOL_REQUESTED_DATE <= @END_DATE

			SELECT @PRICE = AVG(COOL_UNIT_COST) FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL, SALES_ORDER SO 
				WHERE COOL.SALESORDER_ID = SO.SALESORDER_ID 
					AND SO.CUSTOMER_ID LIKE @CUSTID_LIKE
					AND COOL.Product_ID LIKE @PRODID_LIKE
					AND SO.SO_SALES_PERSON LIKE (CASE WHEN @SPID_LIKE = '%' THEN @SPID_LIKE ELSE (select SalesPerson_Name from Sales_Persons WHERE SalesPerson_ID = @SPID) END) 
					AND COOL_STATUS != 'SHIPPED' 
					AND COOL_REQUESTED_DATE >= @START_DATE 
					AND COOL_REQUESTED_DATE <= @END_DATE
		 END
		INSERT INTO @RESULT_TABLE (
								SNAPSHOT_MONTH,
								SNAPSHOT_YEAR,
								MONTH,
								YEAR,
								QTY,
								PRICE,
								CONFIDENCE_LEVEL,
								SNAPSHOT_NAME)
			VALUES ( @CURRENT_MONTH, @CURRENT_YEAR, @MONTH, @YEAR, @QTY, @PRICE, 0, 'Actual Backlog')
		SET @MONTH = @MONTH + 1
		SET @MONTH_COUNT = @MONTH_COUNT + 1
	 END

	SELECT * FROM @RESULT_TABLE	
 END
